﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using DTO;
using System.Data.SqlClient;

namespace Persistence
{
    public class PersonController
    {
        //Crea un comando y le asigna los parametros comunes a todos los tipos especificos de persona.
        //Devuelve el comando creado.
        protected static MySqlCommand insertPerson(Person per, string storeProc)
        {
            //Se crea un comando.
            MySqlCommand comm = DBConnection.getInstance().createCommand();
            //Se le asigna el tipo.
            comm.CommandType = CommandType.StoredProcedure;
            //Se le asigna el nombre del procedimiento almacenado.
            comm.CommandText = storeProc;
            //Se asignan los parametros al comando.
            comm.Parameters.AddWithValue("@pfstname", truncate(per.StrFstName, 40));
            comm.Parameters.AddWithValue("@psecname", per.StrSecName != null ? truncate(per.StrSecName, 40): null);
            comm.Parameters.AddWithValue("@pfstsurname", truncate(per.StrFstSurname, 40));
            comm.Parameters.AddWithValue("@psecsurname", per.StrSecSurname != null ? truncate(per.StrSecSurname, 40): null);
            comm.Parameters.AddWithValue("@pmail", truncate(per.StrEmail, 40));
            comm.Parameters.AddWithValue("@ptelephone", truncate(per.StrTelephone, 20));
            comm.Parameters.AddWithValue("@pcellphone", truncate(per.StrCellphone, 20));
            comm.Parameters.AddWithValue("@paddress", truncate(per.StrAddress, 100));
            comm.Parameters.AddWithValue("@pphotopath", truncate(per.StrPhotoPath, 100));
            comm.Parameters.AddWithValue("@pdoctype", Convert.ToInt16(per.DtDocType.LngId));
            comm.Parameters.AddWithValue("@pdocument", truncate(per.StrDocument, 20));
            comm.Parameters.AddWithValue("@pcomments", per.StrComments != null ? truncate(per.StrComments, 500): null);
            //Se retorna el comando con los parametros insertados.
            return comm;
        }
        //Valida que el nombre de usuario ingresado y el password se correspondan con los de un usuario activo en el 
        //sistema.
        public static long validateUser(string vUser, string vPass)
        {
            DBConnection dbConn = DBConnection.getInstance();
            MySqlCommand comm = dbConn.createCommand();
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "proout_login";
            long uId;
            MySqlParameter outIdParam = new MySqlParameter("@vId", MySqlDbType.Int16)
            {
                Direction = ParameterDirection.Output
            };
            comm.Parameters.AddWithValue("@vuser", vUser);
            comm.Parameters.AddWithValue("@vpass", vPass);
            comm.Parameters.Add(outIdParam);

            dbConn.executeNonQueryCommand(comm);

            uId = long.Parse(outIdParam.Value.ToString());

            return uId;

        }


        //Metodo que da baja logica a una persona en el sistema.
        protected static void deletePerson(long vId, string vRole)
        {
            DBConnection dbConn = DBConnection.getInstance();
            string sql_1 = "UPDATE students SET stustate = 0 WHERE student_id = @pid;";
            string sql_2 = "UPDATE users SET usstate = 0 WHERE usid = @pid;";
            MySqlCommand cmd = dbConn.createCommand();
            //Si el rol de la persona que se quiere dar de baja es administrador o docente...
            if (vRole.Equals("Administrator") || vRole.Equals("Teacher"))
            {
                cmd.CommandText = sql_2;
            }
            else
            //Si el rol de la persona que se quiere dar de baja es estudiante...
            {
                cmd.CommandText = sql_1;
            }
            //Se asignan los parametros a la consulta.
            cmd.Parameters.AddWithValue("pid", Convert.ToInt16(vId));
            //Se ejecuta el comando.
            dbConn.executeNonQueryCommand(cmd);
        }
        //Actualiza en la tabla people, uno a uno, solo los campos cuyas propiedades correspondientes
        //cambiaron con respecto al objeto original que fue obtenido con anterioridad.
        protected static void updatePerson(Person pOriginal, Person pUpdated)
        {
            
            if (!pUpdated.StrFstName.Equals(pOriginal.StrFstName))
            {
                Helper.updateProperty("people", "pefstname", pUpdated.StrFstName,"peid", pOriginal.LngId, 40);
            }
            if (pUpdated.StrSecName != null && !pUpdated.StrSecName.Equals(pOriginal.StrSecName))
            {
                Helper.updateProperty("people", "pesecname", pUpdated.StrSecName, "peid", pOriginal.LngId, 40);
            }
            if (!pUpdated.StrFstSurname.Equals(pOriginal.StrFstSurname))
            {
                Helper.updateProperty("people", "pefstsurname", pUpdated.StrFstSurname, "peid", pOriginal.LngId, 40);
            }
            if (pUpdated.StrSecSurname != null && !pUpdated.StrSecSurname.Equals(pOriginal.StrSecSurname))
            {
                Helper.updateProperty("people", "pesecsurname", pUpdated.StrSecSurname, "peid", pOriginal.LngId, 40);
            }
            if (!pUpdated.StrEmail.Equals(pOriginal.StrEmail))
            {
                Helper.updateProperty("people", "pemail", pUpdated.StrEmail, "peid", pOriginal.LngId, 40);
            }
            if (!pUpdated.StrTelephone.Equals(pOriginal.StrTelephone))
            {
                Helper.updateProperty("people", "petelephone", pUpdated.StrTelephone, "peid", pOriginal.LngId, 20);
            }
            if (!pUpdated.StrCellphone.Equals(pOriginal.StrCellphone))
            {
                Helper.updateProperty("people", "pecellphone", pUpdated.StrCellphone, "peid", pOriginal.LngId, 20);
            }
            if (!pUpdated.StrAddress.Equals(pOriginal.StrAddress))
            {
                Helper.updateProperty("people", "peaddress", pUpdated.StrAddress, "peid", pOriginal.LngId, 100);
            }
            if (!pUpdated.StrPhotoPath.Equals(pOriginal.StrPhotoPath))
            {
                Helper.updateProperty("people", "pephotopath", pUpdated.StrPhotoPath, "peid", pOriginal.LngId, 100);
            }
            if (!pUpdated.DtDocType.LngId.Equals(pOriginal.DtDocType.LngId))
            {
                Helper.updateProperty("people", "pedoctype", pUpdated.DtDocType.LngId, "peid", pOriginal.LngId);
            }
            if (!pUpdated.StrDocument.Equals(pOriginal.StrDocument))
            {
                Helper.updateProperty("people", "pedocument", pUpdated.StrDocument, "peid", pOriginal.LngId, 20);
            }
            if (pUpdated.StrComments != null && !pUpdated.StrComments.Equals(pOriginal.StrComments))
            {
                Helper.updateProperty("people", "pecomments", pUpdated.StrComments, "peid", pOriginal.LngId, 500);
            }
        }
        


        public static Person getPersonById(long vId, Person per)
        { 
            string sql = "SELECT * FROM people WHERE peid = @id;";
            MySqlCommand comm = DBConnection.getInstance().createCommand();
            comm.CommandText = sql;
            comm.Parameters.AddWithValue("id", Convert.ToInt16(vId));

            //Se devuelve una instancia de persona
            return parseRegToPerson(DBConnection.getInstance().executeSelectCommand(comm), per);
        }

        //Obtiene el rol de un usuario o alumno por su id.
        public static string getRoleById(long vId)
        {
            string sql = "SELECT ususertype FROM users WHERE usid = @id;";
            DBConnection dbConn = DBConnection.getInstance();
            MySqlCommand comm = dbConn.createCommand();
            comm.CommandText = sql;
            comm.Parameters.AddWithValue("id", Convert.ToInt16(vId));
            //Si no encuentra el id indicado en la tabla users, asume que el rol es "estudiante"
            return !dbConn.executeScalarCommand(comm).Equals("") ? dbConn.executeScalarCommand(comm) : "Student";
        }

        public static Person parseRegToPerson(DataTable pTable, Person aPerson)
        {
            //Recibe como parametro una DataTable con el registro correspondiente a una
            //persona y devuelve una instancia de Person con los datos obtenidos.

            aPerson.LngId = long.Parse(pTable.Rows[0]["peid"].ToString());
            aPerson.StrFstName = pTable.Rows[0]["pefstname"].ToString();
            aPerson.StrSecName = pTable.Rows[0]["pesecname"].ToString() != "" ? pTable.Rows[0]["pesecname"].ToString(): null;
            aPerson.StrFstSurname = pTable.Rows[0]["pefstsurname"].ToString();
            aPerson.StrSecSurname = pTable.Rows[0]["pesecsurname"].ToString() != "" ? pTable.Rows[0]["pesecsurname"].ToString(): null;
            aPerson.StrEmail = pTable.Rows[0]["pemail"].ToString();
            aPerson.StrTelephone = pTable.Rows[0]["petelephone"].ToString();
            aPerson.StrCellphone = pTable.Rows[0]["pecellphone"].ToString();
            aPerson.StrAddress = pTable.Rows[0]["peaddress"].ToString();
            aPerson.StrPhotoPath = pTable.Rows[0]["pephotopath"].ToString();
            aPerson.DtDocType = DocTypeController.getDocTypeById(Convert.ToInt16(pTable.Rows[0]["pedoctype"].ToString()));
            aPerson.StrDocument = pTable.Rows[0]["pedocument"].ToString();
            aPerson.StrComments = pTable.Rows[0]["pecomments"].ToString() != "" ? pTable.Rows[0]["pecomments"].ToString() : null;

            return aPerson;
        }

        private static string truncate(string unSt, int maxLength)
        {
            return unSt.Length <= maxLength ? unSt : unSt.Substring(0, maxLength);
        }
    }
}
